#!/bin/bash

if [ -n "$1" ] ;then
   do_date=$1
else
   do_date=`date -d "-1 day" +%F`
fi

sql="insert overwrite table ads.ads_member_login_three_day
partition(dt='$do_date')
select device_id,count(1) from 
(select device_id, dt,
date_sub(dt,row_number() over(partition by device_id order by dt)) as gid
from dws.dws_member_start_day where dt <= '$do_date'
and dt >= date_sub( '$do_date',6)) as tmp
group by  device_id,gid
having count(1) >=3;"

hive -e "$sql"
